Postgresql hstore 資料型態初步介紹

hstore 是 Postgresql 引進多年的一個強大的套件,
但是較少為人所知.現在就來做一初步的介紹.

首先需要安裝 hstore extension.

create extension hstore;

hstore 是 Key -> Value 成對的集合(Set)資料型態,Key跟Value都是text(string).
每個 key 都是 Unique.
表達的方式為

"key1" => "val1", "key2" => "val2" ...

接著來看建構的方式.

使用轉型的方式,直接將 text 的內容

select 'a=>1,a=>2'::hstore;
+----------+
|  hstore  |
+----------+
| "a"=>"1" |
+----------+

select 'a=>天津風, b=>島風'::hstore;
+----------------------------+
|           hstore           |
+----------------------------+
| "a"=>"天津風", "b"=>"島風" |
+----------------------------+

或是使用建構函數 hsotre(),有四種方式
hstore(record), hstore(text, text) 以及搭配 array 的
hstore(text[]), hstore(text[], text[]).

record方式
select hstore(row('天津風', '島風'))
     , hstore(row(1, 2))
     , hstore(row('1', '2'));

+------------------------------+----------------------+----------------------+
|            hstore            |        hstore        |        hstore        |
+------------------------------+----------------------+----------------------+
| "f1"=>"天津風", "f2"=>"島風"   | "f1"=>"1", "f2"=>"2" | "f1"=>"1", "f2"=>"2" |
+------------------------------+----------------------+----------------------+

會自動給予 key, f1,f2, ...fn 型態.

後續方法都是要我們給定key.

hstore(text, text)方式
select hstore('1', '冬月');

+-------------+
|   hstore    |
+-------------+
| "1"=>"冬月" |
+-------------+

hstore(text[], text[])方式
select hstore(array['1', '2'], array['天津風', '島風']);
+----------------------------+
|           hstore           |
+----------------------------+
| "1"=>"天津風", "2"=>"島風" |
+----------------------------+

可以推測此建構函數是將array展開後,使用hstore(text, text)來配對.

第四種方式是 hstore(text[])
select hstore(array['1', '天津風', '2', '島風'])
     , hstore(array[['3', '秋月'], ['4', '涼月']]);
     
+----------------------------+--------------------------+
|           hstore           |          hstore          |
+----------------------------+--------------------------+
| "1"=>"天津風", "2"=>"島風"  | "3"=>"秋月", "4"=>"涼月"   |
+----------------------------+--------------------------+

將array展開,依序解析 key->value pair.
也可以這樣

select hstore(array['1', '天津風', '2', '島風'])
    || hstore(array[['3', '秋月'], ['4', '涼月']]);

+------------------------------------------------------+
|                       ?column?                       |
+------------------------------------------------------+
| "1"=>"天津風", "2"=>"島風", "3"=>"秋月", "4"=>"涼月"    |
+------------------------------------------------------+

看完建構式接著來看運算子,經過這段時間,相信大家對 pg_catalog 應該很熟悉了.
可以透過以下查詢

select lpad(replace(oprleft::regtype::text, '-', ''), 10, ' ')
    || ' ' || rpad(oprname, 4, ' ')
    || ' ' || rpad(oprright::regtype::text, 6, ' ')
    || ' = ' || oprresult::regtype::text as expr
     , oprcode::regproc
  from pg_catalog.pg_operator
 where oprleft::regtype = 'hstore'::regtype
    or oprright::regtype = 'hstore'::regtype
    or oprresult::regtype = 'hstore'::regtype
 order by oprname desc, oprleft, oprright, oprresult;

+-------------------------------------+------------------+
|                expr                 |     oprcode      |
+-------------------------------------+------------------+
|     hstore ~    hstore = boolean    | hs_contained     |
|     hstore ||   hstore = hstore     | hs_concat        |
|     hstore @>   hstore = boolean    | hs_contains      |
|     hstore @    hstore = boolean    | hs_contains      |
|     hstore ?|   text[] = boolean    | exists_any       |
|     hstore ?&   text[] = boolean    | exists_all       |
|     hstore ?    text   = boolean    | exist            |
|     hstore =    hstore = boolean    | hstore_eq        |
|     hstore <@   hstore = boolean    | hs_contained     |
|     hstore <>   hstore = boolean    | hstore_ne        |
|     hstore ->   text   = text       | fetchval         |
|     hstore ->   text[] = text[]     | slice_array      |
|     hstore -    text   = hstore     | miku.delete      |
|     hstore -    text[] = hstore     | miku.delete      |
|     hstore -    hstore = hstore     | miku.delete      |
|            %%   hstore = text[]     | hstore_to_array  |
|            %#   hstore = text[]     | hstore_to_matrix |
|     hstore #>=# hstore = boolean    | hstore_ge        |
|     hstore #>#  hstore = boolean    | hstore_gt        |
| anyelement #=   hstore = anyelement | populate_record  |
|     hstore #<=# hstore = boolean    | hstore_le        |
|     hstore #<#  hstore = boolean    | hstore_lt        |
+-------------------------------------+------------------+
(22 rows)

可是官網文件上只列出了14個,可以觀察到有一些常見的像是 = <> ~ 在官網文件上省略掉了,
歸類到其他部分.或是功能相同,但是使用了不同的運算子.
因為有些是舊式符號如 ~ , 後來改用 <@.
有些是 hstore 函數的對應,也有為了函數使用的運算子,本身不直接使用的,如 #=
由此可知,對 pg_catalog 的理解,有助於我們更有系統化的來理解 Postgresql.

來看一些例子

select a.ddteam, b.amatsukaze, c.fuyuzuki
     , b.amatsukaze ~ a.ddteam as "amatsukaze contained ddteam"
     , c.fuyuzuki <@ a.ddteam as "fuyuzuki contained ddteam"
     , a.ddteam @> b.amatsukaze as "ddteam contains amatsukaze"
     , a.ddteam @ c.fuyuzuki as "ddteam contains fuyuzuki"
     , a.ddteam || c.fuyuzuki as "ddteam concat fuyuzuki, replace amatsukaze"
  from (values (hstore(array['1', '2'], array['天津風','島風']))) a (ddteam)
     , (values (hstore('1', '天津風'))) b (amatsukaze)
     , (values ('1=>冬月'::hstore)) c (fuyuzuki); 

+-[ RECORD 1 ]-------------------------------+----------------------------+
| ddteam                                     | "1"=>"天津風", "2"=>"島風" |
| amatsukaze                                 | "1"=>"天津風"              |
| fuyuzuki                                   | "1"=>"冬月"                |
| amatsukaze contained ddteam                | t                          |
| fuyuzuki contained ddteam                  | f                          |
| ddteam contains amatsukaze                 | t                          |
| ddteam contains fuyuzuki                   | f                          |
| ddteam concat fuyuzuki, replace amatsukaze | "1"=>"冬月", "2"=>"島風"   |
+--------------------------------------------+----------------------------+

最後的欄位,因為天津風跟冬月的key都是1, 而hstore 的key是unique的, 新的取代舊的.

關於 key 的

select a.ddteam
     , ddteam ? '3' as "ddteam contain key 1?"
     , ddteam ?& array['1','2'] as "ddteam contain all ['1','2']?"
     , ddteam ?| array['4','5'] as "ddteam contain any ['4','5']?"
  from (values (hstore(array['1', '2'], array['天津風','島風']))) a (ddteam); 

+-[ RECORD 1 ]------------------+----------------------------+
| ddteam                        | "1"=>"天津風", "2"=>"島風"   |
| ddteam contain key 1?         | f                          |
| ddteam contain all ['1','2']? | t                          |
| ddteam contain any ['4','5']? | f                          |
+-------------------------------+----------------------------+

關於 fetch value 的; 結果會是 text 與 text[] 兩種型態.

select ddteam
     , ddteam -> '2' as "ddteam fetch key 2"
     , ddteam -> '3' as "ddteam fetch key 3"
     , ddteam -> array['1', '2'] as "ddteam fetch key [1,2]"
     , ddteam -> array['1', '2', '3'] as "ddteam fetch key [1,2,3]"
  from (values (hstore(array['1', '2'], array['天津風','島風']))) a (ddteam); 

+-[ RECORD 1 ]-------------+----------------------------+
| ddteam                   | "1"=>"天津風", "2"=>"島風" |
| ddteam fetch key 2       | 島風                       |
| ddteam fetch key 3       | ¤                          |
| ddteam fetch key [1,2]   | {天津風,島風}              |
| ddteam fetch key [1,2,3] | {天津風,島風,NULL}         |
+--------------------------+----------------------------+

沒有對應value的就會顯示 NULL, 因為我在psqlrc 裡面設定 null 顯示為 ¤. 
但是進入 array 的, 在顯示時,psql 未將其改成用 ¤ 顯示.
查驗看看

select (ddteam -> '3' ) is null as "is null in ddtem #3"
     , ((ddteam -> array['1', '2', '3'])[3])  is null as "is null in array #3"
  from (values (hstore(array['1', '2'], array['天津風','島風']))) a (ddteam);

+---------------------+---------------------+
| is null in ddtem #3 | is null in array #3 |
+---------------------+---------------------+
| t                   | t                   |
+---------------------+---------------------+

今天先介紹到此.